from __future__ import annotations

from datetime import datetime
from typing import Optional, List, Dict, Any

import pandas as pd
from loguru import logger

from core.database import DatabaseManager, db_manager
from dao.models import BacktestResult


def _fmt_date(v: Any) -> str:
    if v is None:
        return ""
    if isinstance(v, (datetime,)):
        return v.strftime("%Y-%m-%d")
    s = str(v)
    if len(s) == 8 and s.isdigit():
        return f"{s[:4]}-{s[4:6]}-{s[6:8]}"
    return s


def _get_int(stats: Dict[str, Any], key: str) -> Optional[int]:
    try:
        v = stats.get(key)
        if v is None:
            return None
        return int(v)
    except Exception:
        return None


def _get_float(stats: Dict[str, Any], key: str) -> Optional[float]:
    try:
        v = stats.get(key)
        if v is None:
            return None
        return float(v)
    except Exception:
        return None


class BacktestResultDao:
    def __init__(self, db_manager_in: DatabaseManager | None = None):
        self.db_manager = db_manager_in or db_manager

    def save_backtest_result(
        self,
        params: Dict[str, Any],
        statistics: Dict[str, Any],
        results_df: pd.DataFrame | None = None,
    ) -> Optional[int]:
        try:
            for p in [
                'strategy_name', 'strategy_file_path', 'vt_symbol', 'interval',
                'start_date', 'end_date', 'capital', 'rate', 'slippage', 'size', 'pricetick',
            ]:
                if p not in params:
                    logger.error(f"Missing required param: {p}")
                    return None

            session = self.db_manager.get_session()
            try:
                row = BacktestResult(
                    strategy_name=str(params['strategy_name']),
                    strategy_file_path=str(params['strategy_file_path']),
                    vt_symbol=str(params['vt_symbol']),
                    interval=str(params['interval']),
                    start_date=_fmt_date(params['start_date']),
                    end_date=_fmt_date(params['end_date']),
                    capital=int(params['capital']),
                    rate=float(params['rate']),
                    slippage=float(params['slippage']),
                    size=int(params['size']),
                    pricetick=float(params['pricetick']),

                    total_days=_get_int(statistics, 'total_days'),
                    profit_days=_get_int(statistics, 'profit_days'),
                    loss_days=_get_int(statistics, 'loss_days'),
                    end_balance=_get_float(statistics, 'end_balance'),
                    max_drawdown=_get_float(statistics, 'max_drawdown'),
                    max_ddpercent=_get_float(statistics, 'max_ddpercent'),
                    max_drawdown_duration=_get_int(statistics, 'max_drawdown_duration'),
                    total_net_pnl=_get_float(statistics, 'total_net_pnl'),
                    daily_net_pnl=_get_float(statistics, 'daily_net_pnl'),
                    total_commission=_get_float(statistics, 'total_commission'),
                    daily_commission=_get_float(statistics, 'daily_commission'),
                    total_slippage=_get_float(statistics, 'total_slippage'),
                    daily_slippage=_get_float(statistics, 'daily_slippage'),
                    total_turnover=_get_float(statistics, 'total_turnover'),
                    daily_turnover=_get_float(statistics, 'daily_turnover'),
                    total_trade_count=_get_int(statistics, 'total_trade_count'),
                    daily_trade_count=_get_float(statistics, 'daily_trade_count'),
                    total_return=_get_float(statistics, 'total_return'),
                    annual_return=_get_float(statistics, 'annual_return'),
                    daily_return=_get_float(statistics, 'daily_return'),
                    win_rate=_get_float(statistics, 'win_rate'),
                    sharpe_ratio=_get_float(statistics, 'sharpe_ratio'),
                    ewm_sharpe=_get_float(statistics, 'ewm_sharpe'),
                    return_std=_get_float(statistics, 'return_std'),
                    return_drawdown_ratio=_get_float(statistics, 'return_drawdown_ratio'),
                    create_time=datetime.utcnow(),
                    update_time=datetime.utcnow(),
                )
                session.add(row)
                session.commit()
                session.refresh(row)
                return row.id
            except Exception:
                session.rollback()
                raise
            finally:
                session.close()
        except Exception as e:
            logger.exception(f"Error saving backtest result: {e}")
            return None

    def get_backtest_result(self, result_id: int) -> Optional[Dict[str, Any]]:
        session = self.db_manager.get_session()
        try:
            row = session.get(BacktestResult, result_id)
            if not row:
                return None
            return self._to_dict(row)
        finally:
            session.close()

    def get_all_backtest_results(self, limit: int = 100, offset: int = 0) -> List[Dict[str, Any]]:
        session = self.db_manager.get_session()
        try:
            rows = (
                session.query(BacktestResult)
                .order_by(BacktestResult.create_time.desc())
                .offset(offset)
                .limit(limit)
                .all()
            )
            return [self._to_summary_dict(r) for r in rows]
        finally:
            session.close()

    def get_backtest_results_by_strategy(self, strategy_name: str) -> List[Dict[str, Any]]:
        session = self.db_manager.get_session()
        try:
            rows = (
                session.query(BacktestResult)
                .filter(BacktestResult.strategy_name == strategy_name)
                .order_by(BacktestResult.create_time.desc())
                .all()
            )
            return [self._to_summary_dict(r) for r in rows]
        finally:
            session.close()

    def delete_backtest_result(self, result_id: int) -> bool:
        session = self.db_manager.get_session()
        try:
            row = session.get(BacktestResult, result_id)
            if not row:
                return False
            session.delete(row)
            session.commit()
            return True
        except Exception:
            session.rollback()
            raise
        finally:
            session.close()

    @staticmethod
    def _to_dict(r: BacktestResult) -> Dict[str, Any]:
        d = {
            'id': r.id,
            'strategy_name': r.strategy_name,
            'strategy_file_path': r.strategy_file_path,
            'vt_symbol': r.vt_symbol,
            'interval': r.interval,
            'start_date': r.start_date,
            'end_date': r.end_date,
            'capital': r.capital,
            'rate': r.rate,
            'slippage': r.slippage,
            'size': r.size,
            'pricetick': r.pricetick,
            'total_days': r.total_days,
            'profit_days': r.profit_days,
            'loss_days': r.loss_days,
            'end_balance': r.end_balance,
            'max_drawdown': r.max_drawdown,
            'max_ddpercent': r.max_ddpercent,
            'max_drawdown_duration': r.max_drawdown_duration,
            'total_net_pnl': r.total_net_pnl,
            'daily_net_pnl': r.daily_net_pnl,
            'total_commission': r.total_commission,
            'daily_commission': r.daily_commission,
            'total_slippage': r.total_slippage,
            'daily_slippage': r.daily_slippage,
            'total_turnover': r.total_turnover,
            'daily_turnover': r.daily_turnover,
            'total_trade_count': r.total_trade_count,
            'daily_trade_count': r.daily_trade_count,
            'total_return': r.total_return,
            'annual_return': r.annual_return,
            'daily_return': r.daily_return,
            'win_rate': r.win_rate,
            'sharpe_ratio': r.sharpe_ratio,
            'ewm_sharpe': r.ewm_sharpe,
            'return_std': r.return_std,
            'return_drawdown_ratio': r.return_drawdown_ratio,
            'create_time': r.create_time.isoformat() if r.create_time else None,
            'update_time': r.update_time.isoformat() if r.update_time else None,
        }
        return d

    @staticmethod
    def _to_summary_dict(r: BacktestResult) -> Dict[str, Any]:
        return {
            'id': r.id,
            'strategy_name': r.strategy_name,
            'vt_symbol': r.vt_symbol,
            'interval': r.interval,
            'start_date': r.start_date,
            'end_date': r.end_date,
            'capital': r.capital,
            'total_return': r.total_return,
            'annual_return': r.annual_return,
            'max_ddpercent': r.max_ddpercent,
            'sharpe_ratio': r.sharpe_ratio,
            'create_time': r.create_time.isoformat() if r.create_time else None,
            'strategy_file_path': r.strategy_file_path,
        }

